In typical database systems, users store, update and retrieve information by submitting commands to a database server. To be correctly processed, the commands must comply with the database language that is supported by the database server. One popular database language is known as Structured Query Language (SQL).
Using SQL, DML commands can be constructed to specify operations that insert, modify, and/or delete data that exists in a database. At times, it is important for a user that is performing a DML operation to know what values were involved in the operation, and to perform subsequent operations on those values. For example, a user may want to know the old values that were deleted in a delete operation, the old values that were overwritten in an update operation, and/or the new values that were written in an update operation. For the purpose of explanation, the set of values involved in a prior operation, which are to be used in a subsequent operation, are referred to herein as “values-of-interest” (e.g., the “values-of-interest” can be which ever of the old values and the new values are of interest for performing aggregation operations).
In database systems that support SQL, a “returning” clause may be used to obtain a value-of-interest. For example, assume that a user wants to increment a value in a column, C1, of particular row (where key=5), and have the database server return the new value that was written during the update operation. The following code segment could be used to perform the desired operation:
update T  set C1 = C1 + 1  where key = 5returning C1
Unfortunately, the conventional implementation of the returning clause only returns a single value-of-interest. Therefore, programmers must resort to more complex techniques in situations where (1) a first operation changes many values, thereby creating many values-of-interest, and (2) an aggregate operation is to be performed on the values-of-interest associated with the first operation. One such technique is referred to herein as the iterative technique.
According to the iterative technique, the first operation (which changes many values) is performed by iteratively executing code that only changes a single value. During each iteration, the returning clause is used to return the value-of-interest associated with that iteration. For example, assume that a user wants to increment several values in C1, and to keep track of all of the new, post-update values that are changed during the update operation. Such an operation could be performed using the following code segment.
For All i=1....N update T  set C1 = C1 + 1  where key = :input[i]returning C1 in :output [i];
This code segment updates values in the column C1 of table T by adding a 1 to the numbers stored in column C1 of various rows of table T. The statement “where key=:input[i]”, above is an instruction to use the array input[ ] to determine which rows are update (in this specification, although grammatically incorrect, the comma was placed outside of the quotation marks to avoid ambiguities regarding the syntax).
For example, assume that input[ ] stores the following values:                input[1]=1        input[2]=3        input[3]=7and that table T initially has the following rows:        
KEYC112324532410859569710
During the first iteration, the “where” clause will be “where key=1”, since i=1 and input[1]=1. Thus, during the first iteration, the value 23 in the first row of table T is updated to 24, and the value 24 is stored in output[1].
During the second iteration, since i=2 and input[2]=3, the value 2 is updated to 3 in the third row of table T of C1, and the value 3 is stored in output[2]. During the third iteration, since i=3 and input[3]=7, the seventh element of C1, 10, is updated to 11 and the value 11 is stored in output[3]. Thus, after finishing first three iterations, output[ ] has the values [24, 3, 11], and the updated table T has the following values:
KEYC112424533410859569711
Once all of the values-of-interest are captured in the output array, aggregate value may be computed based on the captured values-of-interest. For example, the sum of the captured values-of-interest may be computed by executing the code segment:
s=0;For i =1...N s=s+:output[i];
This code segment initializes the variable s to have the value 0, with the statement s=0. Then, in the for-loop for each element specified by the output array, output[i], the value of the specified element is added to the variable s. The end result is that s will contain a value that represents the sum of all values-of-interest that were captured in output[ ].
The returning clause and the above scheme may be used to return the updated value involved in an operation that makes changes within a database, or return the old value that were deleted or overwritten by an operation. Extensions “.old” and “.new” may be added to the returned “data_item” to signify that the aggregation is performed based on the old or newly updated values of the set of data being modified, respectively. For example, in the above technique using an expression having sum(C1.old) would cause a summation of the old values of the column C1 to be performed.
FIG. 1 shows a simplified database system 100, and illustrates the transfer of data during the execution of the operation described above. Database system 100 includes Relational Database Management System (RDMS) 102, having Structured Query Language (SQL) engine 104. Database system 100 also includes client side stub 108, which is used by application program 109 to interface with a server side stub 106 of the RDMS 102. Application program 109 may be a program and/or another computer system. Application program 109 and/or client side stub 108 may reside on a separate processor or computer than RDMS 102. Alternatively, application program 109 and/or client side stub 108 may reside on the same processor as RDMS 102, but be part of a different process than RDMS 102.
Paths 110, 112, 114, 116, 118, and 120 are used for sending data between the components that they connect. When the above two program segments execute, external program 109 sends statements via path 118 through client side stub 108 and then along path 110 to RDMS 102. The statements are sent along path 112 and interpreted by SQL engine 104. After column C1 is updated, the updated data is returned along path 114 through server side stub 106, along path 116 to client side stub 108, and then finally along path 120 back to external program 109.
It should be noted that in the present example, the user was only interested in the sum of the old values, not in the individual old values themselves. However, for the sum of the old values to be calculated, all of the values-of-interest are returned. Performing aggregate functions using the iterative approach not only returns unnecessary data from the server to the client but may also require an evaluation of the aggregation by the application 109 on the client side. Thus, conventional methods of performing aggregate functions on values-of-interest are inefficient. Therefore, it is desirable to provide techniques for performing aggregate functions on values-of-interest that do not involve the programming complexity and wasteful data movement as experienced using conventional techniques.